package test.testList;

import org.apache.commons.codec.binary.StringUtils;
import test.bean.TdaTable;
import test.bean.TdaTableColumn;

import java.io.File;
import java.io.FileOutputStream;
import java.util.*;

public class ExcelToSql {

    public static void createSql(String filePath, int startRow, boolean isExcel2003) throws Exception{
        Map<String, TdaTable> map = ExcelToTable.readExcel(filePath, startRow, isExcel2003);

        Collection<TdaTable> tdaTables = map.values();
        Iterator<TdaTable> iterator = tdaTables.iterator();

        while (iterator.hasNext()){
            TdaTable table = iterator.next();
            //表名
            String tableName = table.getTableName();
            String outputFilePath = "C:/Users/hspcadmin/Desktop/tda建表语句/" + tableName + ".sql";
            FileOutputStream fileOutputStream = new FileOutputStream(new File(outputFilePath));
            //建表语句
            StringBuilder tableSql = new StringBuilder("create table ");
            tableSql.append(tableName).append("\n(\n");
            //表注释
            StringBuilder annotationSql = new StringBuilder("comment on table ");
            annotationSql.append(tableName).append("\n  is \'").append(table.getFunctionDescription()).append("\';\n");

            List<TdaTableColumn> tdaTableColumnList = table.getTdaTableColumnList();

            if(tdaTableColumnList != null && tdaTableColumnList.size() > 0) {
                for (int i = 0; i < tdaTableColumnList.size(); i++) {
                    TdaTableColumn tdaTableColumn = tdaTableColumnList.get(i);
                    //字段
                    String columnNumber = tdaTableColumn.getColumnNumber();
                    //类型
                    String dataTypeAndPrecision = tdaTableColumn.getDataTypeAndPrecision();
                    //是否为空
                    String nullAble = tdaTableColumn.getNullAble();
                    //默认值
                    String defaultValue = tdaTableColumn.getDefaultValue();
                    if(defaultValue.endsWith(".0")){
                        defaultValue = defaultValue.substring(defaultValue.length()-2);
                    }
                    //字段名称
                    String columnName = tdaTableColumn.getColumnName();

                    tableSql.append("  ").append(columnNumber).append("    ").append(dataTypeAndPrecision);
                    if (!org.springframework.util.StringUtils.isEmpty(defaultValue) && !StringUtils.equals("时间戳（年月日时分秒）", defaultValue) && !StringUtils.equals("uuid", defaultValue) && !StringUtils.equals("序列", defaultValue)) {
                        tableSql.append(" default(\'").append(defaultValue).append("\')");
                    }
                    if (StringUtils.equals("N", nullAble)) {
                        tableSql.append(" not null");
                    }
                    if (i < tdaTableColumnList.size() - 1) {
                        tableSql.append(",\n");
                    }

                    annotationSql.append("comment on column ").append(tableName).append(".").append(columnNumber).append("\n  is \'").append(columnName).append("\';\n");
                }
            }
            tableSql.append("\n)\n");

            //业务主键
            String[] businessPrimaryKeys = table.getBusinessPrimaryKey();
            StringBuilder uniqueSql = new StringBuilder(" ");
            if(businessPrimaryKeys != null && businessPrimaryKeys.length > 0){
                String businessPrimaryKey = businessPrimaryKeys[0];
                if(businessPrimaryKey != null && businessPrimaryKey != "") {
                    //create unique index UNI1_DQPC_PRBL_DETAIL on DQPC_PRBL_DETAIL(CHECK_RES_STATS_MAG_ID,PRBL_DETAIL_ID) nologging  local;
                    uniqueSql.append("create unique index uni1_");
                    uniqueSql.append(tableName).append(" on ").append(tableName).append("(");
                    for (int i = 0; i < businessPrimaryKeys.length; i++) {
                        uniqueSql.append(businessPrimaryKeys[i]);
                        if (i < businessPrimaryKeys.length - 1) {
                            uniqueSql.append(",");
                        }
                    }
                    uniqueSql.append(") nologging  local;\n");
                }
            }

            String[] partitionFields= table.getPartitionField();
            //表主键
            String[] tablePrimaryKeys = table.getTablePrimaryKey();

            StringBuilder primaryKeySql = null;
            if(partitionFields != null && partitionFields.length > 0){
                String partitionField = partitionFields[0];
                //有分区
                if(partitionField != null && partitionField != ""){
                    tableSql.append("partition by list (");
                    for (int i = 0; i < partitionFields.length; i++) {
                        tableSql.append(partitionFields[i]);
                        if (i < partitionFields.length - 1) {
                            tableSql.append(",");
                        }
                    }
                    tableSql.append(")\n").append("(\n").append("  partition PART_DEFAULT values (\'0\')").append("\n);\n");

                    if(tablePrimaryKeys !=null && tablePrimaryKeys.length > 0){
                        String tablePrimaryKey = tablePrimaryKeys[0];
                        if(tablePrimaryKey != null && tablePrimaryKey != "") {
                            uniqueSql.append("create unique index uni2_");
                            uniqueSql.append(tableName).append(" on ").append(tableName).append("(").append(partitionField).append(",");
                            for (int i = 0; i < tablePrimaryKeys.length; i++) {
                                uniqueSql.append(tablePrimaryKeys[i]);
                                if (i < tablePrimaryKeys.length - 1) {
                                    uniqueSql.append(",");
                                }
                            }
                            uniqueSql.append(") nologging  local;\n");
                        }
                    }

                }else {
                    tableSql.append(";");
                    if(tablePrimaryKeys !=null && tablePrimaryKeys.length > 0){
                        String tablePrimaryKey = tablePrimaryKeys[0];
                        if(tablePrimaryKey != null && tablePrimaryKey != "") {
                            primaryKeySql = new StringBuilder("alter table ");
                            //alter table 表名 add constraint 主键名 primary key(字段名1,字段名2……)
                            primaryKeySql.append(tableName).append(" add constraint ").append("pk_").append(tableName).append(" primary key(");
                            for (int i = 0; i < tablePrimaryKeys.length; i++) {
                                primaryKeySql.append(tablePrimaryKeys[i]);
                                if (i < tablePrimaryKeys.length - 1) {
                                    primaryKeySql.append(",");
                                }
                            }
                            primaryKeySql.append(");\n");
                        }
                    }
                }
            }
            //换行
            String newLine = System.getProperty("line.separator");

            fileOutputStream.write(tableSql.toString().getBytes());
            fileOutputStream.write(newLine.getBytes());

            fileOutputStream.write(annotationSql.toString().getBytes());
            fileOutputStream.write(newLine.getBytes());

            if(primaryKeySql != null) {
                fileOutputStream.write(primaryKeySql.toString().getBytes());
                fileOutputStream.write(newLine.getBytes());
            }
            if(uniqueSql != null){
                fileOutputStream.write(uniqueSql.toString().getBytes());
            }

            //关流
            fileOutputStream.close();
        }

    }
}
